CREATE OR REPLACE PACKAGE BODY pkg_device_data
IS


   PROCEDURE Device_TDE_synced (
       p_device_id           VARCHAR2 
	  ,p_from_date           VARCHAR2 
	  ,p_to_date             VARCHAR2 
	  ,p_rowct        OUT   PLS_INTEGER
      )
	IS 
	   v_from_date  date;
	   v_to_date    date;
	   v_rowct      pls_integer;
	   
	BEGIN
	
		IF p_from_date IS NULL OR p_to_date IS NULL THEN
			SELECT nvl(MAX(sync_dt)+1/24/60/60, date '2010-01-01') , sysdate   
			INTO v_from_date, v_to_date 
			FROM device_data_hist 
			WHERE device_id = p_device_id;
		ELSE 
			v_from_date := to_date(p_from_date, 'yyyy/mm/dd hh24:mi:ss');
			v_to_date := to_date(p_to_date, 'yyyy/mm/dd hh24:mi:ss');
			
		END IF ;
	
		dbms_output.put_line('Synced Date Window:'||to_char(v_from_date,'yyyy/mm/dd hh24:mi:ss')||' ~ '||to_char(v_to_date,'yyyy/mm/dd hh24:mi:ss'));
		
		UPDATE device_data_hist d 
		SET sync_dt = sysdate 
		WHERE  d.device_id = p_device_id --- 'TEST_DEV_12_1_4'
		  AND d.eff_dt >= v_from_date 
		  AND d.eff_dt <  v_to_date 
		;
		
		v_rowct := sql%rowcount;
		
		commit;
		p_rowct := v_rowct ;
		
	END Device_TDE_synced;

   PROCEDURE Device_TDE_sync (
	   p_data          OUT   SYS_REFCURSOR 
      ,p_device_id           VARCHAR2 
	  ,p_from_date           VARCHAR2 
	  ,p_to_date             VARCHAR2 
      )
	IS 
	   v_from_date  date;
	   v_to_date    date;
	   
	BEGIN
	
		IF p_from_date IS NULL OR p_to_date IS NULL THEN
			SELECT nvl(MAX(sync_dt)+1/24/60/60, date '2010-01-01') , sysdate   
			INTO v_from_date, v_to_date 
			FROM device_data_hist 
			WHERE device_id = p_device_id;
		ELSE 
			v_from_date := to_date(p_from_date, 'yyyy/mm/dd hh24:mi:ss');
			v_to_date := to_date(p_to_date, 'yyyy/mm/dd hh24:mi:ss');
			
		END IF ;
	
	dbms_output.put_line('Date Window:'||to_char(v_from_date,'yyyy/mm/dd hh24:mi:ss')||' ~ '||to_char(v_to_date,'yyyy/mm/dd hh24:mi:ss'));
	
		OPEN p_data 
		FOR

WITH d
     AS (SELECT d.device_id,
                d.eff_dt,
                d.surface_temperature,
                d.deep_temperature,
                d.surface_oxy,
                d.deep_oxy,
                d.ph_value,
                d.other_value,
                d.oxy_pole_status,
                d.surface_water_pump_status,
                d.deep_water_pump_status,
                d.ph_sensor_status,
                d.ammonia_nitrogen_status,
                d.other_status,
                d.temp_status,
                d.power_supply_status
           FROM device_data_hist d
--                INNER JOIN device_list l ON d.device_id = l.device_id
--                INNER JOIN pond_info p ON p.pond_id = d.pond_id
          WHERE 1=1 -- p.user_id = 7                                     -- p_user_id
          AND d.device_id = p_device_id --- 'TEST_DEV_12_1_4'
         --   and   p.pond_id = p_pond_id
		  AND d.eff_dt >= v_from_date 
		  AND d.eff_dt <  v_to_date 
         UNION ALL
         SELECT p_device_id                               --d.device_id
               ,TO_DATE ('2999/01/01 0:0:0', 'yyyy/mm/dd hh24:mi:ss') -- d.eff_dt
               ,0                                     -- d.surface_temperature
               ,0                                        -- d.deep_temperature
               ,0                                             -- d.surface_oxy
               ,0                                                -- d.deep_oxy
               ,0                                                -- d.ph_value
               ,0                                             -- d.other_value
               ,' '                                       -- d.oxy_pole_status
               ,' '                             -- d.surface_water_pump_status
               ,' '                                -- d.deep_water_pump_status
               ,' '                                      -- d.ph_sensor_status
               ,' '                               -- d.ammonia_nitrogen_status
               ,' '                                          -- d.other_status
               ,' '                                           -- d.temp_status
               ,' '                                   -- d.power_supply_status
           FROM DUAL)
  SELECT  '#;'|| d.device_id  || ';'  ||       
		ROW_NUMBER () OVER (ORDER BY eff_dt)         || ',' || 
		TO_CHAR (d.eff_dt, 'yyyy/mm/dd hh24:mi:ss')  || ',' || 
		TO_CHAR (d.surface_temperature, 'fm990.0')   || ',' || 
		TO_CHAR (d.deep_temperature, 'fm990.0')      || ',' || 
		TO_CHAR (d.surface_oxy, 'fm990.0')           || ',' || 
		TO_CHAR (d.deep_oxy, 'fm990.0')              || ',' || 
		TO_CHAR (d.ph_value, 'fm990.0')              || ',' || 
		TO_CHAR (d.other_value, 'fm990.0')           || ',' || 
		NVL (d.oxy_pole_status, ' ')                 || ',' || 
		NVL (d.surface_water_pump_status, ' ')       || ',' || 
		NVL (d.deep_water_pump_status, ' ')          || ',' || 
		NVL (d.ph_sensor_status, ' ')                || ',' || 
		NVL (d.ammonia_nitrogen_status, ' ')         || ',' || 
		NVL (d.other_status, ' ')                    || ',' || 
		NVL (d.temp_status, ' ')                     || ',' || 
		NVL (d.power_supply_status, ' ')             || ';$;'  
            AS data                            
    FROM d
ORDER BY eff_dt
		;

		
	  
	END Device_TDE_sync;
	 
	 

   PROCEDURE Dev_Data_HistHourly (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data          OUT   SYS_REFCURSOR
      ,p_user_id             PLS_INTEGER
	  ,p_pond_id             VARCHAR2
	  ,p_from_date           VARCHAR2
	  ,p_to_date             VARCHAR2
      )
	IS
	    v_flag          PLS_INTEGER := 0 ;
		v_from_date     DATE ;
		v_to_date       DATE ;

	BEGIN

		BEGIN
			v_from_date := to_date ( p_from_date, 'yyyy/mm/dd');
			v_to_date   := to_date ( p_to_date, 'yyyy/mm/dd') + 1 ;
		EXCEPTION
			WHEN OTHERS THEN
			p_flag :=  -1;
			p_ret_msg  := '参数错误，输入日期不符合格式yyyy/mm/dd: 从： '||p_from_date ||' 到 '||p_to_date  ;

			OPEN p_data
			FOR SELECT ''
			FROM DUAL
			WHERE NULL IS NOT NULL ;

			RETURN ;
		END;

		OPEN p_data
		FOR
   with dh as (
                select device_id, trunc(eff_dt, 'hh24') as_of_date
                ,max(SURFACE_TEMPERATURE) max_surface_temp
                ,avg(SURFACE_TEMPERATURE) avg_surface_temp
                ,min(SURFACE_TEMPERATURE) min_surface_temp
                ,max(DEEP_TEMPERATURE)    max_deep_temp
                ,avg(DEEP_TEMPERATURE)    avg_deep_temp
                ,min(DEEP_TEMPERATURE)    min_deep_temp
                ,max(SURFACE_OXY)         max_surface_oxy
                ,avg(SURFACE_OXY)         avg_surface_oxy
                ,min(SURFACE_OXY)         min_surface_oxy
                ,max(DEEP_OXY)            max_deep_oxy
                ,avg(DEEP_OXY)            avg_deep_oxy
                ,min(DEEP_OXY)            min_deep_oxy
                ,max(PH_VALUE)            max_ph_value
                ,avg(PH_VALUE)            avg_ph_value
                ,min(PH_VALUE)            min_ph_value
                ,count(1)                 ds_count
                from device_data_hist
                where eff_dt >= v_from_date   and eff_dt < v_to_date
                and pond_id =  p_pond_id
                group by device_id, trunc(eff_dt, 'hh24')
            )
			SELECT d.device_id
				  ,l.device_type
				  ,p.pond_name
			      ,TO_CHAR(as_of_date,'yyyy/mm/dd hh24:mi')   as_of_date
			      ,TO_CHAR(d.max_surface_temp ,'fm990.0')  max_surface_temp
			      ,TO_CHAR(d.avg_surface_temp ,'fm990.0')  avg_surface_temp
			      ,TO_CHAR(d.min_surface_temp ,'fm990.0')  min_surface_temp
			      ,TO_CHAR(d.max_deep_temp    ,'fm990.0')  max_deep_temp
			      ,TO_CHAR(d.avg_deep_temp    ,'fm990.0')  avg_deep_temp
			      ,TO_CHAR(d.min_deep_temp    ,'fm990.0')  min_deep_temp
			      ,TO_CHAR(d.max_surface_oxy  ,'fm990.0')  max_surface_oxy
			      ,TO_CHAR(d.avg_surface_oxy  ,'fm990.0')  avg_surface_oxy
			      ,TO_CHAR(d.min_surface_oxy  ,'fm990.0')  min_surface_oxy
			      ,TO_CHAR(d.max_deep_oxy     ,'fm990.0')  max_deep_oxy
			      ,TO_CHAR(d.avg_deep_oxy     ,'fm990.0')  avg_deep_oxy
			      ,TO_CHAR(d.min_deep_oxy     ,'fm990.0')  min_deep_oxy
			      ,TO_CHAR(d.max_ph_value     ,'fm990.0')  max_ph_value
			      ,TO_CHAR(d.avg_ph_value     ,'fm990.0')  avg_ph_value
			      ,TO_CHAR(d.min_ph_value     ,'fm990.0')  min_ph_value
			      ,TO_CHAR(d.ds_count         ,'fm990'  )  ds_count
			FROM dh d
			INNER join device_list l on d.device_id = l.device_id
			INNER join pond_info p on p.pond_id = p_pond_id
			 AND  p.user_id = p_user_id
			order by device_id, as_of_date  desc 			;

			p_flag :=  0;
			p_ret_msg  := '打开游标成功，塘口：'||p_pond_id  ||',日期范围，从:'||p_from_date||' 到：'|| p_to_date  ;

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := 'NO_DATA_FOUND, '||  replace( sqlerrm(), '"', '') ; -- errmsg

			OPEN p_data
			FOR SELECT ''
			FROM DUAL
			WHERE NULL IS NOT NULL ;



		WHEN OTHERS  THEN
			p_flag :=  -1;
			p_ret_msg  := '未知错误 , '||  replace( sqlerrm(), '"', '') ; -- errmsg

			OPEN p_data
				FOR SELECT ''
				FROM DUAL
				WHERE NULL IS NOT NULL ;

	END Dev_Data_HistHourly ;



   PROCEDURE Dev_Data_HistDaily (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data          OUT   SYS_REFCURSOR
      ,p_user_id             PLS_INTEGER
	  ,p_pond_id             VARCHAR2
	  ,p_from_date           VARCHAR2
	  ,p_to_date             VARCHAR2
      )
	IS
	    v_flag          PLS_INTEGER := 0 ;
		v_from_date     DATE ;
		v_to_date       DATE ;

	BEGIN

		BEGIN
			v_from_date := to_date ( p_from_date, 'yyyy/mm/dd');
			v_to_date   := to_date ( p_to_date, 'yyyy/mm/dd') + 1 ;
		EXCEPTION
			WHEN OTHERS THEN
			p_flag :=  -1;
			p_ret_msg  := '参数错误，输入日期不符合格式yyyy/mm/dd: 从： '||p_from_date ||' 到 '||p_to_date  ;

			OPEN p_data
			FOR SELECT ''
			FROM DUAL
			WHERE NULL IS NOT NULL ;

			RETURN ;
		END;

		OPEN p_data
		FOR
   with dh as (
                select device_id, trunc(eff_dt) as_of_date
                ,max(SURFACE_TEMPERATURE) max_surface_temp
                ,avg(SURFACE_TEMPERATURE) avg_surface_temp
                ,min(SURFACE_TEMPERATURE) min_surface_temp
                ,max(DEEP_TEMPERATURE)    max_deep_temp
                ,avg(DEEP_TEMPERATURE)    avg_deep_temp
                ,min(DEEP_TEMPERATURE)    min_deep_temp
                ,max(SURFACE_OXY)         max_surface_oxy
                ,avg(SURFACE_OXY)         avg_surface_oxy
                ,min(SURFACE_OXY)         min_surface_oxy
                ,max(DEEP_OXY)            max_deep_oxy
                ,avg(DEEP_OXY)            avg_deep_oxy
                ,min(DEEP_OXY)            min_deep_oxy
                ,max(PH_VALUE)            max_ph_value
                ,avg(PH_VALUE)            avg_ph_value
                ,min(PH_VALUE)            min_ph_value
                ,count(1)                 ds_count
                from device_data_hist
                where eff_dt >= v_from_date   and eff_dt < v_to_date
                and pond_id =  p_pond_id
                group by device_id, trunc(eff_dt)
            )
			SELECT d.device_id
				  ,l.device_type
				  ,p.pond_name
			      ,TO_CHAR(as_of_date,'yyyy/mm/dd')        as_of_date
			      ,TO_CHAR(d.max_surface_temp ,'fm990.0')  max_surface_temp
			      ,TO_CHAR(d.avg_surface_temp ,'fm990.0')  avg_surface_temp
			      ,TO_CHAR(d.min_surface_temp ,'fm990.0')  min_surface_temp
			      ,TO_CHAR(d.max_deep_temp    ,'fm990.0')  max_deep_temp
			      ,TO_CHAR(d.avg_deep_temp    ,'fm990.0')  avg_deep_temp
			      ,TO_CHAR(d.min_deep_temp    ,'fm990.0')  min_deep_temp
			      ,TO_CHAR(d.max_surface_oxy  ,'fm990.0')  max_surface_oxy
			      ,TO_CHAR(d.avg_surface_oxy  ,'fm990.0')  avg_surface_oxy
			      ,TO_CHAR(d.min_surface_oxy  ,'fm990.0')  min_surface_oxy
			      ,TO_CHAR(d.max_deep_oxy     ,'fm990.0')  max_deep_oxy
			      ,TO_CHAR(d.avg_deep_oxy     ,'fm990.0')  avg_deep_oxy
			      ,TO_CHAR(d.min_deep_oxy     ,'fm990.0')  min_deep_oxy
			      ,TO_CHAR(d.max_ph_value     ,'fm990.0')  max_ph_value
			      ,TO_CHAR(d.avg_ph_value     ,'fm990.0')  avg_ph_value
			      ,TO_CHAR(d.min_ph_value     ,'fm990.0')  min_ph_value
			      ,TO_CHAR(d.ds_count         ,'fm990'  )  ds_count
			FROM dh d
			INNER join device_list l on d.device_id = l.device_id
			INNER join pond_info p on p.pond_id = p_pond_id
			 AND  p.user_id = p_user_id
			order by device_id, as_of_date  desc 			;

			p_flag :=  0;
			p_ret_msg  := '打开游标成功，塘口：'||p_pond_id  ||',日期范围，从:'||p_from_date||' 到：'|| p_to_date  ;

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := 'NO_DATA_FOUND, '||  replace( sqlerrm(), '"', '') ; -- errmsg

			OPEN p_data
			FOR SELECT ''
			FROM DUAL
			WHERE NULL IS NOT NULL ;



		WHEN OTHERS  THEN
			p_flag :=  -1;
			p_ret_msg  := '未知错误 , '||  replace( sqlerrm(), '"', '') ; -- errmsg

			OPEN p_data
				FOR SELECT ''
				FROM DUAL
				WHERE NULL IS NOT NULL ;

	END Dev_Data_HistDaily ;


   PROCEDURE Dev_Data_HistList (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data          OUT   SYS_REFCURSOR
      ,p_user_id             PLS_INTEGER
	  ,p_pond_id             VARCHAR2
	  ,p_qry_date            VARCHAR2
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_qry_date     DATE ;

	BEGIN

		BEGIN
			v_qry_date := to_date ( p_qry_date, 'yyyy/mm/dd');
		EXCEPTION
			WHEN OTHERS THEN
			p_flag :=  -1;
			p_ret_msg  := '参数错误，输入日期不符合格式yyyy/mm/dd: '||p_qry_date ;

			OPEN p_data
			FOR SELECT ''
			FROM DUAL
			WHERE NULL IS NOT NULL ;

			RETURN ;
		END;

		OPEN p_data
		FOR
			SELECT d.device_id
				  ,l.device_type
				  ,p.pond_name
			      ,TO_CHAR(round(d.eff_dt,'mi'),'hh24:mi')      updated_time
			      ,TO_CHAR(d.surface_temperature   ,'fm990.0')  surface_temp
			      ,TO_CHAR(d.deep_temperature      ,'fm990.0')  deep_temp
			      ,TO_CHAR(d.surface_oxy           ,'fm990.0')  surface_oxy
			      ,TO_CHAR(d.deep_oxy              ,'fm990.0')  deep_oxy
			      ,TO_CHAR(d.ph_value              ,'fm990.0')  ph_value
                  ,NVL(d.oxy_pole_status            ,'""')		oxy_pole_status
                  ,NVL(d.surface_water_pump_status  ,'""')		surface_water_pump_status
                  ,NVL(d.deep_water_pump_status     ,'""')		deep_water_pump_status
                  ,NVL(d.ph_sensor_status           ,'""')		ph_sensor_status
                  ,NVL(d.ammonia_nitrogen_status    ,'""')		ammonia_nitrogen_status
                  ,NVL(d.other_status               ,'""')		other_status
                  ,NVL(d.temp_status                ,'""')		temp_status
                  ,NVL(d.power_supply_status        ,'""')		power_supply_status
			FROM device_data_hist d
			INNER join device_list l on d.device_id = l.device_id
			INNER join pond_info p on p.pond_id = d.pond_id
			WHERE p.user_id = p_user_id
			and   p.pond_id = p_pond_id
			and   eff_dt BETWEEN v_qry_date AND v_qry_date + 1
			order by device_id, eff_dt  desc
			;

			p_flag :=  0;
			p_ret_msg  := '打开游标成功，塘口：'||p_pond_id  ||',日期yyyy/mm/dd:'||p_qry_date ;

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := 'NO_DATA_FOUND, '||  replace( sqlerrm(), '"', '') ; -- errmsg

			OPEN p_data
			FOR SELECT ''
			FROM DUAL
			WHERE NULL IS NOT NULL ;



		WHEN OTHERS  THEN
			p_flag :=  -1;
			p_ret_msg  := '未知错误 , '||  replace( sqlerrm(), '"', '') ; -- errmsg

			OPEN p_data
				FOR SELECT ''
				FROM DUAL
				WHERE NULL IS NOT NULL ;

	END Dev_Data_HistList ;

/*
   PROCEDURE Realtime_PondInfo (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id             PLS_INTEGER
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);

	BEGIN

		select
			   ' "device_ct":"'||count(1)  ||'"'||
			   ',"online_dev_ct":"'|| nvl(sum ( case when sysdate-d.incoming_dt < 5/60/24
							then 1 else 0 end ),0)   ||'"'||
			   ',"devices":['||
						listagg(  '{"device_id":"'               || d.device_id                                                      ||'"'||
								  ',"device_type":"'             || l.device_type	                                                 ||'"'||
								  ',"pond_id":"'                 || d.pond_id	                                                     ||'"'||
								  ',"online_flag":"'             || case when sysdate - d.incoming_dt < 5 / 60/ 24 then 1 else 0 end ||'"'||
								  ',"surface_temperature":"'     || d.surface_temperature	                                         ||'"'||
								  ',"deep_temperature":"'        || d.deep_temperature	                                             ||'"'||
								  ',"surface_oxy":"'             || d.surface_oxy	                                                 ||'"'||
								  ',"deep_oxy":"'                || d.deep_oxy	                                                     ||'"'||
--								  ',"temperature_max":"'         || t.temperature_max	                                             ||'"'||
								  ',"temperature_min":"'         || nvl(t.temperature_min,0)                                         ||'"'||
								  ',"oxy_min":"'                 || nvl(t.oxy_min	     ,0)                                         ||'"'||
								  ',"ph_value":"'                || nvl(d.ph_value	     ,0)                                         ||'"'||
								  ',"ph_min":"'                  || nvl(t.ph_min	     ,0)                                         ||'"'||
								  ',"ph_max":"'                  || nvl(t.ph_max	     ,0)                                         ||'"'||
								  ',"updated_time":"'            || to_char(d.incoming_dt,'yyyy/mm/dd hh24:mi:ss')                   ||'"'||
								  '}' , ',' )
							  WITHIN GROUP ( order by device_sn  DESC )
						  ||'] }'
		   into v_msg
		from device_data d
		inner join device_list l on d.device_id = l.device_id
		inner join pond_info p on p.pond_id = d.pond_id
		left join device_sensor_threshold t on t.device_id = d.device_id
		where p.user_id = p_user_id
		 ;


		p_flag := 0;

		p_ret_msg := '成功读取塘口最新数据';
		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'",'||v_msg ; -- ||'}';

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := '查询失败，该用户名下没有任何设备';
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||' ,"device_ct":"'||0 ||'"'
						  ||' ,"online_dev_ct":"'|| 0  ||'"'
						  ||' ,"devices":[] }';

		WHEN OTHERS  THEN
			p_flag := -2 ;
			p_ret_msg := '查询设备失败，未知错误：'||replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||' ,"device_ct":"'||0 ||'"'
						  ||' ,"online_dev_ct":"'|| 0  ||'"'
						  ||' ,"devices":[] }';

	END Realtime_PondInfo ;
*/
	FUNCTION Realtime_Data (
--       p_flag          OUT   PLS_INTEGER,
--	  p_ret_msg       OUT   VARCHAR2,
      p_user_id             PLS_INTEGER		
	)
	RETURN TBL_RealTime_Data PIPELINED
	AS 
	BEGIN
		FOR r IN (
			SELECT '{"pond_id":'||p.pond_id
				   ||',"pond_name":"'||p.pond_name ||'"'
				   ||',"devices":['
				   || LISTAGG(
							 '{"device_id":"'    ||d.device_id      ||'"'
						  ||',"device_type":"'   ||l.device_type    ||'"'
						  ||',"online_flag":'    ||CASE WHEN SYSDATE - d.incoming_dt < 5 / 60/ 24 THEN 1 ELSE 0 END
						  ||',"surface_temp":'   ||TO_CHAR(d.surface_temperature ,'fm9990.0')
						  ||',"deep_temp":'      ||TO_CHAR(d.deep_temperature    ,'fm9990.0')
						  ||',"surface_oxy":'    ||TO_CHAR(d.surface_oxy         ,'fm9990.0')
						  ||',"deep_oxy":'       ||TO_CHAR(d.deep_oxy            ,'fm9990.0')
						  ||',"ph_value":'       ||TO_CHAR(d.ph_value            ,'fm9990.0')
						  ||',"oxy_pole_status":'          ||'"'||NVL(d.oxy_pole_status            ,'') ||'"'
						  ||',"surface_water_pump_status":'||'"'||NVL(d.surface_water_pump_status  ,'') ||'"'
						  ||',"deep_water_pump_status":'   ||'"'||NVL(d.deep_water_pump_status     ,'') ||'"'
						  ||',"ph_sensor_status":'         ||'"'||NVL(d.ph_sensor_status           ,'') ||'"'
						  ||',"ammonia_nitrogen_status":'  ||'"'||NVL(d.ammonia_nitrogen_status    ,'') ||'"'
						  ||',"other_status":'             ||'"'||NVL(d.other_status               ,'') ||'"'
						  ||',"temp_status":'              ||'"'||NVL(d.temp_status                ,'') ||'"'
						  ||',"power_supply_status":'      ||'"'||NVL(d.power_supply_status        ,'') ||'"'
						  ||',"updated_time":"'  ||TO_CHAR(d.incoming_dt,'yyyy/mm/dd hh24:mi:ss')||'"'
						  ||'}'  , ',') WITHIN GROUP (ORDER BY d.device_sn DESC )
				   ||']}' AS pond_data 
			FROM device_data d
			INNER join device_list l on d.device_id = l.device_id
			INNER join pond_info p on p.pond_id = l.pond_id  --- for real time data, device_list.pond_id must be current poond_info.pond_id
							  --   and l.pond_id = p.pond_id
								--  and ( nvl(l.pond_id,0) = 0  or l.pond_id = p.pond_id)
	--		LEFT join device_sensor_threshold t on t.device_id = l.device_id
			WHERE p.user_id = p_user_id
			GROUP by p.pond_id, p.pond_name
		)
		LOOP 
		  PIPE ROW ( r );
		END LOOP;
		

		--p_flag := 0;

		--p_ret_msg := '成功读取塘口最新数据';

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			--p_flag :=  1;
			--p_ret_msg  := '读取塘口实时数据失败， NO_DATA_FOUND';
			null;

		WHEN OTHERS  THEN
			--p_flag := -2 ;
			--p_ret_msg := '读取塘口实时数据失败，未知错误：'||replace( sqlerrm(), '"', '');
		null;
	
	
	END Realtime_Data;
	

   PROCEDURE Realtime_PondInfo (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data          OUT   SYS_REFCURSOR
      ,p_user_id             PLS_INTEGER
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);

	BEGIN

		OPEN p_data
		FOR
		SELECT '{"pond_id":'||p.pond_id
			   ||',"pond_name":"'||p.pond_name ||'"'
			   ||',"devices":['
			   || LISTAGG(
						 '{"device_id":"'    ||d.device_id      ||'"'
					  ||',"device_type":"'   ||l.device_type    ||'"'
					  ||',"online_flag":'    ||CASE WHEN SYSDATE - d.incoming_dt < 5 / 60/ 24 THEN 1 ELSE 0 END
					  ||',"surface_temp":'   ||TO_CHAR(d.surface_temperature ,'fm9990.0')
					  ||',"deep_temp":'      ||TO_CHAR(d.deep_temperature    ,'fm9990.0')
					  ||',"surface_oxy":'    ||TO_CHAR(d.surface_oxy         ,'fm9990.0')
					  ||',"deep_oxy":'       ||TO_CHAR(d.deep_oxy            ,'fm9990.0')
					  ||',"ph_value":'       ||TO_CHAR(d.ph_value            ,'fm9990.0')
                      ||',"oxy_pole_status":'          ||'"'||NVL(d.oxy_pole_status            ,'') ||'"'
                      ||',"surface_water_pump_status":'||'"'||NVL(d.surface_water_pump_status  ,'') ||'"'
                      ||',"deep_water_pump_status":'   ||'"'||NVL(d.deep_water_pump_status     ,'') ||'"'
                      ||',"ph_sensor_status":'         ||'"'||NVL(d.ph_sensor_status           ,'') ||'"'
                      ||',"ammonia_nitrogen_status":'  ||'"'||NVL(d.ammonia_nitrogen_status    ,'') ||'"'
                      ||',"other_status":'             ||'"'||NVL(d.other_status               ,'') ||'"'
                      ||',"temp_status":'              ||'"'||NVL(d.temp_status                ,'') ||'"'
                      ||',"power_supply_status":'      ||'"'||NVL(d.power_supply_status        ,'') ||'"'
					  ||',"updated_time":"'  ||TO_CHAR(d.incoming_dt,'yyyy/mm/dd hh24:mi:ss')||'"'
					  ||'}'  , ',') WITHIN GROUP (ORDER BY d.device_sn DESC )
			   ||']}'
		FROM device_data d
		INNER join device_list l on d.device_id = l.device_id
		INNER join pond_info p on p.pond_id = l.pond_id  --- for real time data, device_list.pond_id must be current poond_info.pond_id
		                  --   and l.pond_id = p.pond_id
							--  and ( nvl(l.pond_id,0) = 0  or l.pond_id = p.pond_id)
--		LEFT join device_sensor_threshold t on t.device_id = l.device_id
		WHERE p.user_id = p_user_id
		GROUP by p.pond_id, p.pond_name;

		p_flag := 0;

		p_ret_msg := '成功读取塘口最新数据';

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := '读取塘口实时数据失败， NO_DATA_FOUND';

			OPEN p_data
			FOR SELECT ''
			FROM DUAL
			WHERE NULL IS NOT NULL ;

		WHEN OTHERS  THEN
			p_flag := -2 ;
			p_ret_msg := '读取塘口实时数据失败，未知错误：'||replace( sqlerrm(), '"', '');

			OPEN p_data
				FOR SELECT ''
				FROM DUAL
				WHERE NULL IS NOT NULL ;

	END Realtime_PondInfo ;


   PROCEDURE get_Device_Margin (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id             PLS_INTEGER
      ,p_device_id           VARCHAR2
      )
	IS
	    v_flag          PLS_INTEGER := 0 ;
		v_msg           VARCHAR2(4000);

		v_device_sn     PLS_INTEGER :=0;
		v_data          VARCHAR2(4000);


	BEGIN

		SELECT      '"oxy_min":'          || to_char( NVL(oxy_min         ,0)    ,'fm990.0')
--			||','||	'"oxy_max":'          || to_char( NVL(oxy_max         ,0)    ,'fm990.0')
			||','||	'"ph_min":'           || to_char( NVL(ph_min          ,0)    ,'fm990.0')
			||','||	'"ph_max":'           || to_char( NVL(ph_max          ,0)    ,'fm990.0')
			||','||	'"ammonia_nitrogen":' || to_char( NVL(ammonia_nitrogen,0)    ,'fm990.0')
			||','||	'"temperature_min":'  || to_char( NVL(temperature_min ,0)    ,'fm990.0')
--			||','||	'"temperature_max":'  || temperature_max
		  INTO v_data
		FROM      device_list d
		INNER JOIN pond_info p
		   ON p.pond_id = d.pond_id
		LEFT JOIN  device_sensor_threshold t
           ON t.device_id = d.device_id
		WHERE p.user_id = p_user_id
		  AND d.device_id = p_device_id
		 ;

		COMMIT;

		p_flag := 0;

		p_ret_msg := '已读取设备传感器限值';
		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'",'||v_data||' }';

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := '读取设备传感器限值失败，该用户名下没有找到设备：'||p_device_id ;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||' }';

		WHEN OTHERS  THEN
			p_flag := -2 ;
			p_ret_msg := '读取设备传感器限值失败，未知错误：'||replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||'  }';

	END get_Device_Margin ;



   PROCEDURE Fetch_Device_Align (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id             PLS_INTEGER
      ,p_device_id           VARCHAR2
      )
	IS
	    v_flag          PLS_INTEGER := 0 ;
		v_msg           VARCHAR2(4000);

		v_device_sn     PLS_INTEGER :=0;
		v_data          VARCHAR2(4000);


	BEGIN

		SELECT      '"align_dt":"'             || to_char( incoming_dt，'yyyy/mm/dd hh24:mi:ss') ||'"'
			||','||	'"temperature":'          || to_char( NVL(temperature           ,0)    ,'fm990.0')
			||','||	'"oxy_voltage_mv":'       || to_char( NVL(oxy_voltage_mv        ,0)    ,'fm9990')
			||','||	'"oxy_align_ratio":'      || to_char( NVL(oxy_align_ratio       ,0)    ,'fm99990')
			||','||	'"ph_base_voltage_mv":'   || to_char( NVL(ph_base_voltage_mv    ,0)    ,'fm9990')
			||','||	'"ph_align_ratio":'       || to_char( NVL(ph_align_ratio        ,0)    ,'fm99990')
			||','||	'"ammonia_nitrogen":'     || to_char( NVL(ammonia_nitrogen      ,0)    ,'fm9990')
		  INTO v_data
		FROM  device_list d
		INNER JOIN pond_info p
		   ON p.pond_id = d.pond_id
		INNER JOIN  device_align a
		   ON a.device_id = d.device_id
		INNER JOIN dev_incoming_log l
		   ON l.dev_incoming_logid = a.dev_incoming_logid
		WHERE p.user_id = p_user_id
		  AND d.device_id = p_device_id
		;


		COMMIT;

		p_flag := 0;

		p_ret_msg := '已读取设备校准数据';
		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'",'||v_data||' }';

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := '读取设备校准数据失败，该用户名下没有找到设备：'||p_device_id ;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||' }';

		WHEN OTHERS  THEN
			p_flag := -2 ;
			p_ret_msg := '读取设备校准数据失败，未知错误：'||replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||'  }';

	END Fetch_Device_Align ;


   PROCEDURE Cmd_Device_Align (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id             PLS_INTEGER
      ,p_device_id           VARCHAR2
	  ,p_created_by          VARCHAR2
      )
	IS
	    v_flag          PLS_INTEGER := 0 ;
		v_msg           VARCHAR2(4000);

		v_device_sn     PLS_INTEGER :=0;
		v_data_type     VARCHAR2(1):='3';
		v_outgoing_data VARCHAR2(1):='1';

	BEGIN

		SELECT device_sn
		  INTO v_device_sn
		FROM      device_list d
		INNER JOIN pond_info p
		   ON p.pond_id = d.pond_id
		WHERE p.user_id = p_user_id
		  AND d.device_id = p_device_id
		 ;

		INSERT INTO dev_outgoing_log ( dev_outgoing_logid, device_sn, device_id, created_by, data_type, outgoing_data  )
		VALUES ( dev_outgoing_log_seq.NEXTVAL,  v_device_sn, p_device_id, substr(p_created_by||'-Dev_Align', 1, 30) , v_data_type, v_outgoing_data);

		COMMIT;

		p_flag := 0;

		p_ret_msg := '校准指令已下达';
		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }';

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := '校准指令失败，该用户名下没有找到设备：'||p_device_id ;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||' }';

		WHEN OTHERS  THEN
			p_flag := -2 ;
			p_ret_msg := '校准指令失败，未知错误：'||replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||'  }';

	END Cmd_Device_Align ;



   PROCEDURE Set_Device_Margin (
       p_flag          OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id             PLS_INTEGER
      ,p_device_id           VARCHAR2
	  ,p_created_by          VARCHAR2
      ,p_oxy_min             NUMBER
      ,p_ph_min              NUMBER
      ,p_ph_max              NUMBER
      ,p_ammonia_nitrogen    NUMBER
      ,p_temperature_min     NUMBER
	  ,p_interface           VARCHAR2 DEFAULT NULL 
      )
	IS
	    v_flag          PLS_INTEGER := 0 ;
		v_msg           VARCHAR2(4000);
		v_interface_msg VARCHAR2(50) :=  CASE WHEN p_interface IS NULL THEN ' ' ELSE ',"interface":"'||p_interface||'"' END ;
		v_device_sn     PLS_INTEGER :=0;
		v_data_type     VARCHAR2(1):='5';
		v_outgoing_data VARCHAR2(50):=
                                      to_char(p_oxy_min           ,'fm000')||','||
                                      to_char(p_ph_min            ,'fm000')||','||
                                      to_char(p_ph_max            ,'fm000')||','||
                                      to_char(p_ammonia_nitrogen  ,'fm000')||','||
                                      to_char(p_temperature_min*10,'fm000');


	BEGIN

		SELECT device_sn
		  INTO v_device_sn
		FROM      device_list d
		INNER JOIN pond_info p
		   ON p.pond_id = d.pond_id
		WHERE p.user_id = p_user_id
		  AND d.device_id = p_device_id
		 ;

		INSERT INTO dev_outgoing_log ( dev_outgoing_logid, device_sn, device_id, created_by, data_type, outgoing_data  )
		VALUES ( dev_outgoing_log_seq.NEXTVAL,  v_device_sn, p_device_id, substr(p_created_by||'-Dev_Margin',1,30), v_data_type, v_outgoing_data);

		COMMIT;

		p_flag := 0;

		p_ret_msg := '已设定设备传感器限值';
		p_data_msg  := '{"flag": '||p_flag||v_interface_msg||',"message": "'|| p_ret_msg||'" }';

		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN
			p_flag :=  1;
			p_ret_msg  := '设定设备传感器限值失败，该用户名下没有找到设备：'||p_device_id ;
			p_data_msg  :=  '{ "flag": '||p_flag ||v_interface_msg
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||' }';

		WHEN OTHERS  THEN
			p_flag := -2 ;
			p_ret_msg := '设定设备传感器限值失败，未知错误：'||replace( sqlerrm(), '"', '');
			p_data_msg := NULL;
			p_data_msg  :=  '{ "flag": '||p_flag
			              ||v_interface_msg
			              ||' ,"message": "'|| p_ret_msg ||'"'
						  ||'  }';

	END Set_Device_Margin ;



END pkg_device_data;
/
